package com.qysoft.rapid.dao.mybatis;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import com.qysoft.rapid.domain.Bean;

/**
 * 组装sql
 * @author liugong
 *
 */
public class SqlBuilder {

	public static String forTableBuilderDoBuild(String tableName) {
		return "select * from `" + tableName + "` where 1 = 2";
	}
	
	public static void buildSave(String tableName, Map<String, Object> attrs, StringBuilder sql, List<Object> paras) {
		sql.append("insert into `").append(tableName).append("`(");
		StringBuilder temp = new StringBuilder(") values(");
		for (Entry<String, Object> e: attrs.entrySet()) {
			String colName = e.getKey();
			if (paras.size() > 0) {
				sql.append(", ");
				temp.append(", ");
			}
			sql.append("`").append(colName).append("`");
			temp.append("?");
			paras.add(e.getValue());
		}
		sql.append(temp.toString()).append(")");
	}
	
	public static void forBeanUpdateColumn(String tableName, String[] pKeys,Object[] ids,Bean bean,StringBuilder sql, List<Object> paras) {
		tableName = tableName.trim();
		trimPrimaryKeys(pKeys);
		sql.append("update ").append(tableName).append(" set ");
		for (Entry<String, Object> e : bean.entrySet()) {
			String colName = e.getKey();
			Set<String> modifyFlag = bean.getModifyFlag();
			if (modifyFlag.contains(colName)) {
				boolean isKey = false;
				for (String pKey : pKeys)	// skip primaryKeys
					if (pKey.equalsIgnoreCase(colName)) {
						isKey = true ;
						break ;
					}
				if (isKey)
					continue;
				
				if (paras.size() > 0)
					sql.append(", ");
				Object value = e.getValue();
				if (value instanceof String && (((String)value).toLowerCase().endsWith(".nextval")|| ((String)value).toLowerCase().equals("sysdate")||((String)value).toLowerCase().startsWith("to_date"))) {
					sql.append(colName).append(" = "+value+" ");
				}else {
					sql.append(colName).append(" = ? ");
					paras.add(value);
				}
			}
		}
		sql.append(" where ");
		for (int i=0; i<pKeys.length; i++) {
			if (i > 0)
				sql.append(" and ");
			sql.append(pKeys[i]).append(" = ?");
			paras.add(ids[i]);
		}
	}
	
	public static String forBeanFindById(String tableName, String columns,String[] pKeys) {
		tableName = tableName.trim();
		trimPrimaryKeys(pKeys);
		StringBuilder sql = new StringBuilder("select ");
		columns = columns.trim();
		if ("*".equals(columns)) {
			sql.append("*");
		}
		else {
			String[] arr = columns.split(",");
			for (int i=0; i<arr.length; i++) {
				if (i > 0)
					sql.append(",");
				sql.append(arr[i].trim());
			}
		}
		sql.append(" from ");
		sql.append(tableName);
		sql.append(" where ");
		for (int i=0; i<pKeys.length; i++) {
			if (i > 0)
				sql.append(" and ");
			sql.append(pKeys[i]).append(" = ?");
		}
		return sql.toString();
	}
	
	public static String forBeanFindById(String tableName, String[] pKeys) {
		tableName = tableName.trim();
		trimPrimaryKeys(pKeys);
		
		StringBuilder sql = new StringBuilder("select * from ").append(tableName).append(" where ");
		for (int i=0; i<pKeys.length; i++) {
			if (i > 0)
				sql.append(" and ");
			sql.append(pKeys[i]).append(" = ?");
		}
		return sql.toString();
	}
	
	public static String forBeanDeleteById(String tableName, String[] pKeys) {
		tableName = tableName.trim();
		trimPrimaryKeys(pKeys);
		
		StringBuilder sql = new StringBuilder("delete from ").append(tableName).append(" where ");
		for (int i=0; i<pKeys.length; i++) {
			if (i > 0)
				sql.append(" and ");
			sql.append(pKeys[i]).append(" = ?");
		}
		return sql.toString();
	}
	
	public static void forBeanSave(StringBuilder sql, List<Object> paras, String tableName, String[] pKeys, Bean bean) {
		tableName = tableName.trim();
		trimPrimaryKeys(pKeys);
		sql.append("insert into ");
		sql.append(tableName).append("(");
		StringBuilder temp = new StringBuilder();
		temp.append(") values(");
		int count = 0;
		for (Entry<String, Object> e: bean.entrySet()) {
			String colName = e.getKey();
			if (count++ > 0) {
				sql.append(", ");
				temp.append(", ");
			}
			sql.append(colName);
			Object value = e.getValue();
			if (value instanceof String && (((String)value).toLowerCase().endsWith(".nextval")|| ((String)value).toLowerCase().equals("sysdate")||((String)value).toLowerCase().startsWith("to_date"))) {
			    temp.append(value);
			} else {
				temp.append("?");
				paras.add(value);
			}
		}
		sql.append(temp.toString()).append(")");
	}
	
	public static void forBeanUpdate(String tableName, String[] pKeys, Object[] ids, Bean bean, StringBuilder sql, List<Object> paras) {
		tableName = tableName.trim();
		trimPrimaryKeys(pKeys);
		sql.append("update ").append(tableName).append(" set ");
		for (Entry<String, Object> e: bean.entrySet()) {
			String colName = e.getKey();
			if (!isPrimaryKey(colName, pKeys)) {
				if (paras.size() > 0) {
					sql.append(", ");
				}
				Object value = e.getValue();
				if (value instanceof String && (((String)value).toLowerCase().endsWith(".nextval")|| ((String)value).toLowerCase().equals("sysdate")||((String)value).toLowerCase().startsWith("to_date"))) {
					sql.append(colName).append(" = "+value+" ");
				} else {
					sql.append(colName).append(" = ? ");
					paras.add(value);
				}
			}
		}
		sql.append(" where ");
		for (int i=0; i<pKeys.length; i++) {
			if (i > 0)
				sql.append(" and ");
			sql.append(pKeys[i]).append(" = ?");
			paras.add(ids[i]);
		}
	}
	
	public static void forPaginate(StringBuilder sql, int pageNumber, int pageSize, String select, String sqlExceptSelect) {
		int offset = pageSize * (pageNumber - 1);
		sql.append(select).append(" ");
		sql.append(sqlExceptSelect);
		sql.append(" limit ").append(offset).append(", ").append(pageSize);	// limit can use one or two '?' to pass paras
	}	
	

	private static void trimPrimaryKeys(String[] pKeys) {
		for (int i=0; i<pKeys.length; i++)
			pKeys[i] = pKeys[i].trim();
	}
	

	private static boolean isPrimaryKey(String colName, String[] pKeys) {
		for (String pKey : pKeys)
			if (colName.equalsIgnoreCase(pKey))
				return true;
		return false;
	}	
	
	static void fillStatement(PreparedStatement pst, List<Object> paras) throws SQLException {
		for (int i=0, size=paras.size(); i<size; i++) {
			pst.setObject(i + 1, paras.get(i));
		}
	}	
	
	static void fillStatement(PreparedStatement pst, Object... paras) throws SQLException {
		for (int i=0; i<paras.length; i++) {
			pst.setObject(i + 1, paras[i]);
		}
	}	
}
